﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_report_employee_swipe')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_report_employee_swipe

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-10 23:57:45
-- Database:    scm_main_hysj20200702
-- Description: 报表管理-员工实际打卡记录
-- =============================================

CREATE PROCEDURE bd_proc_report_employee_swipe
  @dateBegin DATE,
  @dateEnd  DATE,
  @userNameOrNo VARCHAR(100) = '',  --工号或姓名
  @users  VARCHAR(MAX) = '',
  @depts  VARCHAR(MAX) = '',
  @userType  VARCHAR(50) = '',
  @userCompany VARCHAR(200) = '',
  @userStatus  VARCHAR(20) = '',
  @isPage  INT = 1,
  @pageSize  INT = 50,
  @pageNo  INT = 1,
  @rowCount  INT = 0 OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #result
  (
    user_serial BIGINT 
    , user_no VARCHAR(20) 
    , user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , dept_id BIGINT 
    , dept_name VARCHAR(500) COLLATE Chinese_PRC_CI_AS
    , card_no VARCHAR(50) COLLATE Chinese_PRC_CI_AS --员工卡号
    , card_manhour VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --员工属性工时制
    , company VARCHAR(200) COLLATE Chinese_PRC_CI_AS    --员工属性所属公司
    , user_type VARCHAR(50) COLLATE Chinese_PRC_CI_AS   --员工属性员工类型
    , user_status VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --员工属性工作状态
    
    , jl_id INT
    , swipe_time  DATETIME
    , device_id INT
    , device_name VARCHAR(100)
    , gps VARCHAR(100)
    , swipe_type  VARCHAR(100)
  )

--	打卡时间：员工实际打卡时间
--	考勤机编号：员工刷卡的考勤机编号
--	GPS地理位置：手机打卡GPS地理位置
--	打卡类型：手机打卡、刷卡、补出勤


  DECLARE @sql VARCHAR(MAX) = ''

  SELECT @sql = '
    INSERT INTO #result (user_serial, user_no, user_name, dept_id, dept_name, card_no, card_manhour, company, user_type, user_status, 
      jl_id, swipe_time, device_id, device_name, gps, swipe_type)
    SELECT u.user_serial, u.user_no, u.user_lname, u.user_dep, u.dep_name, u.card_number, u.man_hour_system, u.user_company, u.user_sny_lx, u.user_sny_type,
      jl.bh, jl.sj, jl.dev_serial, '''', '''', ''''
    FROM kt_jl jl
    INNER JOIN bd_view_user_all u ON u.user_serial = jl.user_serial
    WHERE 1 = 1 
  '

  SELECT @sql += ' AND jl.sj>=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND jl.sj < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' '


  IF(ISNULL(@userNameOrNo, '') <> '' AND ISNULL(@users, '') = '')
  BEGIN
  	SELECT @sql += ' AND u.user_no LIKE ''%' + @userNameOrNo + '%'' OR u.user_lname LIKE ''%' + @userNameOrNo + '%'''
  END

  IF(ISNULL(@users, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_serial IN(' + @users + ')'
  END

  IF(ISNULL(@depts, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_dep IN(' + @depts + ')'
  END

  IF(ISNULL(@userType, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_sny_lx = ''' + @userType + ''''
  END

  IF(ISNULL(@userCompany, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_company LIKE ''%' + @userCompany + '%'''
  END

  IF(ISNULL(@userStatus, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_sny_type = ''' + @userStatus + ''''
  END

  EXEC(@sql)

  SELECT @rowCount = COUNT(1) FROM #result 

  IF(ISNULL(@isPage, 0) = 0)
  BEGIN
  	SELECT * FROM #result ORDER BY swipe_time
  END ELSE
  BEGIN
    ;WITH result AS (
      SELECT *,RowNum = ROW_NUMBER() OVER (ORDER BY swipe_time) FROM #result 
    )
    SELECT user_serial, user_no, user_name, dept_id, dept_name, card_no, card_manhour, company, user_type, user_status, jl_id, swipe_time, device_id, device_name, gps, swipe_type
    FROM result
    WHERE RowNum BETWEEN (@pageNo - 1) * @pageSize + 1 AND @pageNo * @pageSize	
  END

  DROP TABLE #result

  RETURN @rowCount

  SET NOCOUNT OFF;
END